#-*-coding:utf8-*-
'''
Created on 2014-7-22

@author: Administrator
'''

import sqlite3
import threading


db_file = 'spider.db'

class DBMgr(object):
    _lock = threading.Lock()
    
    def __init__(self):
        self.conn = sqlite3.connect(db_file)
    
    
    @classmethod
    def instance(cls):
        """single patten modle.
        """
        cls._lock.acquire()
        if not hasattr(cls,'_instance'):
            cls._instance = cls()
        
        cls._lock.release()
        return cls._instance
    
    def insert(self,table,**kwargs):
        
        for k,v in kwargs.items():
            if v is None:
                kwargs.pop(k)

        _property = ','.join(kwargs.keys())
        _v = ['"%s"'%v for v in kwargs.values()]
        _values = ','.join(_v)

        _sql = "insert into %s(%s) values(%s);"%(table,
                                        _property,_values)
        self.conn.executescript(_sql)
        
    def query(self,table,colums='*',limit=-1,**query_dic):
        
        condition = ' and '.join(["%s='%s'"%(k,v) for k,v in query_dic.items()])
        if condition:
            condition = 'where ' + condition
        else:
            condition = ''
        
        if type(colums) in [tuple,list]:
            colums = ','.join(colums)
        _sql = "select %s from %s %s"%(colums,table,condition)
        
        if limit != -1:
            _sql += ' limit %d'%limit
        
        cursor = self.conn.cursor()
        try:
            cursor.execute(_sql)
        except sqlite3.OperationalError,e:
            print e
            return None

        rlt = cursor.fetchall()
        return rlt

    def update(self,table,update_dic,**query_dic):
        
        condition = ' and '.join(["%s='%s'"%(k,v) for k,v in query_dic.items()])
        if condition:
            condition = 'where ' + condition
        else:
            condition = ''

        _update = ' and '.join(["%s='%s'"%(k,v) for k,v in update_dic.items()])
        _sql = "update %s set %s %s"%(table,_update,condition)

        self.conn.executescript(_sql)

    def delete(self,table,**query_dic):
        
        condition = ' and '.join(["%s='%s'"%(k,v) for k,v in query_dic.items()])
        if condition:
            condition = 'where ' + condition
        else:
            condition = ''

        _sql = "delete from %s %s"%(table,condition)

        self.conn.executescript(_sql)

        
    def __del__(self):
        self.conn.close()
        

def init():
    import os
    if not os.path.isfile(db_file):
        with open('spider.sql','r') as f:
            sql_script = f.read()
            conn = sqlite3.connect(db_file)
            conn.executescript(sql_script)
            conn.close()


if __name__ == "__main__":
    init()
    #d1 = DBMgr.instance()
    #d1.insert('qiushi',title='糗事百科',context='有趣的段子')
    #print 'ok' in d1.query('qiushi','title')
    #d1.update('qiushi',{'title':'teqwe','flag':'r'},title='dd',**{'id':3})
    #d1.delete('qiushi',**{'id':3})
    

    
